import pandas as pd
import os
import logging
from datetime import datetime

# 配置日志
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    filename='count_blank_values.log',
    filemode='w'
)

console = logging.StreamHandler()
console.setLevel(logging.INFO)
formatter = logging.Formatter('%(asctime)s - %(levelname)s - %(message)s')
console.setFormatter(formatter)
logging.getLogger('').addHandler(console)

logging.info("开始统计初始表中各字段的空白值样本数")

try:
    # 读取初始表
    file_path = 'd:/Desktop/XHSCrawer/V2/results/初始表.xlsx'
    logging.info(f"正在读取文件: {file_path}")
    df = pd.read_excel(file_path)
    
    # 获取总样本数
    total_rows = len(df)
    logging.info(f"初始表总样本数: {total_rows}")
    
    # 创建统计结果DataFrame
    blank_stats = []
    
    # 遍历每个字段，统计空白值
    for column in df.columns:
        # 计算空白值数量（包括NaN、空字符串等）
        blank_count = df[column].isna().sum()
        
        # 检查空字符串
        if df[column].dtype == 'object':
            str_blank_count = (df[column] == '').sum()
            blank_count += str_blank_count
        
        # 计算百分比
        percentage = (blank_count / total_rows * 100) if total_rows > 0 else 0
        
        # 记录统计结果
        blank_stats.append({
            '字段名': column,
            '空白值数量': blank_count,
            '空白值占比(%)': round(percentage, 2)
        })
        
        logging.info(f"字段 '{column}': 空白值数量 = {blank_count}, 占比 = {percentage:.2f}%")
    
    # 创建结果DataFrame并排序（按空白值数量降序）
    result_df = pd.DataFrame(blank_stats)
    result_df = result_df.sort_values('空白值数量', ascending=False)
    
    # 保存结果到Excel文件
    results_dir = 'd:/Desktop/XHSCrawer/V2/results'
    output_file = os.path.join(results_dir, '字段空白值统计.xlsx')
    result_df.to_excel(output_file, index=False)
    logging.info(f"统计结果已保存到: {output_file}")
    
    # 打印统计结果摘要
    print("\n=== 字段空白值统计结果 ===")
    print(f"总样本数: {total_rows}")
    print("\n各字段空白值统计（按空白值数量降序）:")
    print(result_df.to_string(index=False))
    
    # 找出空白值最多的前5个字段
    top_blank_fields = result_df.head(5)
    print("\n空白值最多的5个字段:")
    print(top_blank_fields.to_string(index=False))
    
    logging.info("字段空白值统计完成")
    
    # 输出到日志文件的详细信息
    with open('count_blank_values_detailed.log', 'w', encoding='utf-8') as f:
        f.write(f"字段空白值统计详细结果\n")
        f.write(f"统计时间: {datetime.now().strftime('%Y-%m-%d %H:%M:%S')}\n")
        f.write(f"总样本数: {total_rows}\n\n")
        f.write("各字段空白值详情:\n")
        
        for _, row in result_df.iterrows():
            f.write(f"字段名: {row['字段名']}\n")
            f.write(f"  空白值数量: {row['空白值数量']}\n")
            f.write(f"  空白值占比: {row['空白值占比(%)']}%\n\n")
            
            # 如果空白值大于0，记录这些样本的索引
            if row['空白值数量'] > 0:
                column_name = row['字段名']
                if df[column_name].dtype == 'object':
                    blank_indices = df[(df[column_name].isna()) | (df[column_name] == '')].index.tolist()
                else:
                    blank_indices = df[df[column_name].isna()].index.tolist()
                
                f.write(f"  空白值样本索引（前10个）: {blank_indices[:10]}\n")
                if len(blank_indices) > 10:
                    f.write(f"  空白值样本总数: {len(blank_indices)}\n")
                f.write("\n")
    
    print("\n详细统计信息已保存到 count_blank_values_detailed.log")
    
    # 找出完全没有空白值的字段
    non_blank_fields = result_df[result_df['空白值数量'] == 0]
    if len(non_blank_fields) > 0:
        print(f"\n没有空白值的字段数量: {len(non_blank_fields)}")
        print("这些字段是:")
        print(', '.join(non_blank_fields['字段名'].tolist()))
    
    # 统计空白值占比超过50%的字段
    high_blank_fields = result_df[result_df['空白值占比(%)'] > 50]
    if len(high_blank_fields) > 0:
        print(f"\n空白值占比超过50%的字段数量: {len(high_blank_fields)}")
        print("这些字段是:")
        for _, row in high_blank_fields.iterrows():
            print(f"  - {row['字段名']}: {row['空白值占比(%)']}%")
            
    print("\n统计完成！")
    
except Exception as e:
    logging.error(f"统计过程中出现错误: {str(e)}")
    print(f"错误: {str(e)}")
